package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.stereotype.Repository;

import com.ws.shop.bean.Page;
import com.ws.shop.dao.GoodsDao;
import com.ws.shop.entity.Accessory;
import com.ws.shop.entity.Goods;
import com.ws.shop.util.JdbcUtil;
import com.ws.shop.util.StringUtil;

@Repository(value="goodsDao")
public class GoodsDaoImpl extends BaseDaoImpl implements GoodsDao {
	
	final String findCategoryListByPage_count = "select count(1) from jvf_goods as g left join jvf_category as c on g.cid = c.id";
	final String findCategoryListByPage_list = "select g.id,g.cid,g.goodsname,g.goodsfreight,g.goodspic,g.goodsbrief,g.goodsabout,g.edittime,c.categoryname from jvf_goods as g left join jvf_category as c on g.cid = c.id";
	final String findGoodsByPage_order = " order by g.id desc";
	public Page findGoodsByPage(int pageno,int pagesize, String goodsname) {
		StringBuilder query = new StringBuilder();
		if(!StringUtil.isEmpty(goodsname))
			query.append(" where g.goodsname like '%").append(goodsname).append("%'");
		return this.findPage(pageno, pagesize, new StringBuilder(findCategoryListByPage_count).append(query).toString(), new StringBuilder(findCategoryListByPage_list).append(query)
				.append(findGoodsByPage_order));
	}
	
	final String saveGoods = "insert into jvf_goods(cid,goodsname,goodsfreight,goodsbrief,goodsabout,edittime) values(?,?,?,?,?,now())";
	final String updateGoods = "update jvf_goods set cid=?,goodsname=?,goodsfreight=?,goodsbrief=?,goodsabout=? where id=?";
	public int saveOrUpdate(final Goods goods) {
		return this.execute(new ConnectionCallback<Integer>(){
			public Integer doInConnection(Connection conn) throws SQLException,
					DataAccessException {
				PreparedStatement ps = null;
				ResultSet rs = null;
				try{
					if(goods.getId() == 0){
						ps = conn.prepareStatement(saveGoods, Statement.RETURN_GENERATED_KEYS);
						ps.setInt(1, goods.getCid());
						ps.setString(2, goods.getGoodsname());
						ps.setFloat(3, goods.getGoodsfreight());
						ps.setString(4, goods.getGoodsbrief());
						ps.setString(5, goods.getGoodsabout());
						ps.executeUpdate();
						rs = ps.getGeneratedKeys();
						if(rs.next())
							return rs.getInt(1);
					}else{
						ps = conn.prepareStatement(updateGoods);
						ps.setInt(1, goods.getCid());
						ps.setString(2, goods.getGoodsname());
						ps.setFloat(3, goods.getGoodsfreight());
						ps.setString(4, goods.getGoodsbrief());
						ps.setString(5, goods.getGoodsabout());
						ps.setInt(6, goods.getId());
						ps.executeUpdate();
						return goods.getId();
					}
				}finally{
					JdbcUtil.close(rs, ps, null);
				}
				return 0;
			}
		});
	}
	
	final String deleteGoodsAccessory = "delete from jvf_accessory_relation where relationid=?";
	final String saveGoodsAccessory = "insert into jvf_accessory_relation(accessoryid,relationid,tables) values(?,?,'Goods')";
	public void saveGoodsAccessory(final int id,final int[] imgs) {
		this.update(deleteGoodsAccessory, id);
		this.batchUpdate(saveGoodsAccessory, new BatchPreparedStatementSetter(){
			public int getBatchSize() {
				return imgs.length;
			}
			public void setValues(PreparedStatement ps, int i)
					throws SQLException {
				ps.setInt(1, imgs[i]);
				ps.setInt(2, id);
			}
		});
	}
	
	public void deleteById(int id) {
		String[] sql = new String[2];
		sql[0] = "delete from jvf_goods where id="+id;
		sql[1] = "delete from jvf_accessory_relation where relationid="+id;
		this.batchUpdate(sql);
	}

	final String deleteByIds_1 = "delete from jvf_goods where id=?";
	final String deleteByIds_2 = "delete from jvf_accessory_relation where relationid=?";
	public void deleteByIds(final int[] ids) {
		this.batchUpdate(deleteByIds_1, new BatchPreparedStatementSetter(){
			public int getBatchSize() {
				return ids.length;
			}
			public void setValues(PreparedStatement ps, int i)
					throws SQLException {
				ps.setInt(1, ids[i]);
			}
		});
		this.batchUpdate(deleteByIds_2, new BatchPreparedStatementSetter(){
			public int getBatchSize() {
				return ids.length;
			}
			public void setValues(PreparedStatement ps, int i)
					throws SQLException {
				ps.setInt(1, ids[i]);
			}
		});
	}

	final String findGoodsById = "select cid,goodsname,goodsfreight,goodsbrief,goodsabout,edittime from jvf_goods where id=?";
	public Goods findGoodsById(final int id) {
		return this.execute(findGoodsById, new PreparedStatementCallback<Goods>(){
			public Goods doInPreparedStatement(PreparedStatement ps)
					throws SQLException, DataAccessException {
				ResultSet rs = null;
				try{
					ps.setInt(1, id);
					rs = ps.executeQuery();
					if(rs.next()){
						Goods g = new Goods();
						g.setId(id);
						g.setCid(rs.getInt(1));
						g.setGoodsname(rs.getString(2));
						g.setGoodsfreight(rs.getFloat(3));
						g.setGoodsbrief(rs.getString(4));
						g.setGoodsabout(rs.getString(5));
						g.setEdittime(rs.getTimestamp(6));
						return g;
					}
					return null;
				}finally{
					JdbcUtil.closeResultSet(rs);
				}
			}
			
		});
	}

	final String findAccessoryListByGoodsId = "select b.id,b.type,b.title,b.path,b.size,b.thumbnail,b.uploadtime from jvf_accessory_relation a left join jvf_accessory b on a.accessoryid = b.id where a.relationid = ? and a.tables = 'Goods'";
	public List<Accessory> findAccessoryListByGoodsId(final int goodsId) {
		return this.execute(findAccessoryListByGoodsId, new PreparedStatementCallback<List<Accessory>>(){
			public List<Accessory> doInPreparedStatement(PreparedStatement ps)
					throws SQLException, DataAccessException {
				ResultSet rs = null;
				try{
					List<Accessory> list = new LinkedList<Accessory>();
					ps.setInt(1, goodsId);
					rs = ps.executeQuery();
					Accessory a = null;
					while(rs.next()){
						a = new Accessory();
						a.setId(rs.getInt(1));
						a.setType(rs.getString(2));
						a.setTitle(rs.getString(3));
						a.setPath(rs.getString(4));
						a.setSize(rs.getLong(5));
						a.setThumbnail(rs.getString(6));
						a.setUploadtime(rs.getTimestamp(7));
						list.add(a);
					}
					return list;
				}finally{
					JdbcUtil.closeResultSet(rs);
				}
			}
		});
	}

}
